package com.book.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collections;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.book.constant.Constants;
import com.book.formbean.BookForm;
import com.book.formbean.UserForm;
import com.book.model.BigType;
import com.book.model.Book;
import com.book.model.SmallType;

@Repository
public class BookDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public int findBookCount() {
		String sql = "select count(*) from content";
		return queryBookCount(sql);
	}

	private int queryBookCount(final String sql) {
		return this.jdbcTemplate.queryForInt(sql);
	}

	@SuppressWarnings("unchecked")
	public List<Book> findBooks(String title, String bookIds, String author, int pageNo, int pageSize) {
		StringBuilder sql = new StringBuilder("select * from (select ROW_NUMBER() OVER() AS rownum,content.* from content where 1=1 ");
		if (!Constants.ALL.equals(title)) {
			sql.append("and title like '%" + title + "%' ");
		}
		if (!Constants.ALL.equals(author)) {
			sql.append("and author like '%" + author + "%' ");
		}
		if (bookIds.equals("")) {
			return Collections.EMPTY_LIST;
		}
		if (!Constants.ALL.equals(bookIds)) {
			sql.append("and id in (" + bookIds + ") ");
		}
		sql.append(" order by id desc) as temp where rownum >" + (pageNo - 1) * pageSize + " and rownum<=" + pageNo * pageSize);

		return queryBooks(sql.toString());
	}

	public List<Book> queryBooks(final String sql) {
		return this.jdbcTemplate.query(sql, new BookRowMapper());
	}

	private final class BookRowMapper implements RowMapper<Book> {
		@Override
		public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
			Book book = new Book();
			book.setTitle(rs.getString("title"));
			book.setAuthor(rs.getString("author"));
			book.setDate(rs.getDate("date"));
			book.setId(rs.getInt("id"));
			book.setPath(rs.getString("path"));
			return book;
		}
	}

	public String addBook(final BookForm bookForm, String smallTypes) {
		final String INSERT_SQL = "insert into content (title,author,date,path) values(?,?,?,?)";
		System.out.println(INSERT_SQL);
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(INSERT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
				ps.setString(1, bookForm.getTitle());
				ps.setString(2, bookForm.getAuthor());
				ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
				ps.setString(4, bookForm.getFileData().getOriginalFilename());
				return ps;
			}
		}, keyHolder);
		String bookId = String.valueOf(keyHolder.getKey());

		if (Constants.ALL.equals(smallTypes)) {
			return bookId;
		}
		for (String smallTypeId : smallTypes.split(",")) {
			String sql = "insert into book_to_type(book_id,type_id) values (" + bookId + "," + smallTypeId + ")";
			jdbcTemplate.update(sql);
		}
		return bookId;
	}

	public void delBook(int id) {
		String sql = "delete from content where id =" + id;
		jdbcTemplate.execute(sql);
	}

	public int findUniqueUser(UserForm userForm) {
		return jdbcTemplate.queryForInt("select count(*) from login_user where user_name='" + userForm.getUserName() + "' and password='" + userForm.getPassWord() + "'");
	}

	public List<BigType> findBigTypes() {
		return this.jdbcTemplate.query("select * from big_type", new BookBigTypeMapper());
	}

	private final class BookBigTypeMapper implements RowMapper<BigType> {
		@Override
		public BigType mapRow(ResultSet rs, int rowNum) throws SQLException {
			BigType bigType = new BigType();
			bigType.setId(rs.getInt("id"));
			bigType.setName(rs.getString("name"));
			return bigType;
		}
	}

	public List<SmallType> findSmallTypes(int bigTypeId) {
		return this.jdbcTemplate.query("select * from small_type where bigType_id=?", new Object[] { bigTypeId }, new BookSmallTypeMapper());
	}

	private final class BookSmallTypeMapper implements RowMapper<SmallType> {
		@Override
		public SmallType mapRow(ResultSet rs, int rowNum) throws SQLException {
			SmallType smallType = new SmallType();
			smallType.setId(rs.getInt("id"));
			smallType.setName(rs.getString("name"));
			return smallType;
		}
	}

	public String findBookIds(String typeIds) {
		StringBuffer bookIdsString = new StringBuffer("");
		String sql = "select book_id from book_to_type ";
		if (!Constants.ALL.equals(typeIds)) {
			sql += "where type_id in (" + typeIds + ")";
		} else {
			return Constants.ALL;
		}
		List<Integer> bookIds = jdbcTemplate.query(sql, new BookIdsMapper());
		for (Integer bookId : bookIds) {
			bookIdsString.append(String.valueOf(bookId) + ',');
		}
		if (bookIdsString.length() == 0) {
			return "";
		}
		return bookIdsString.toString().substring(0, bookIdsString.length() - 1);
	}

	private final class BookIdsMapper implements RowMapper<Integer> {
		@Override
		public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
			return rs.getInt("book_id");
		}
	}

	public List<SmallType> findSmallTypesByBookId(int bookId) {
		return this.jdbcTemplate.query("select st.name name,st.id id from small_type st,book_to_type bt where st.id=bt.type_id and  bt.book_id=" + bookId, new BookSmallTypeByBookIdMapper());
	}

	private final class BookSmallTypeByBookIdMapper implements RowMapper<SmallType> {
		@Override
		public SmallType mapRow(ResultSet rs, int rowNum) throws SQLException {
			SmallType smallType = new SmallType();
			smallType.setId(rs.getInt("id"));
			smallType.setName(rs.getString("name"));
			return smallType;
		}
	}

	public void delBookToTypeByBookId(int id) {
		String sql = "delete from book_to_type where book_id =" + id;
		jdbcTemplate.update(sql);
	}

}
